package com.example.demomk.db;

import android.content.Context;

import androidx.annotation.NonNull;
import androidx.annotation.VisibleForTesting;
import androidx.room.Database;
import androidx.room.Room;
import androidx.room.RoomDatabase;
import androidx.room.migration.Migration;
import androidx.sqlite.db.SupportSQLiteDatabase;

import com.example.demomk.Dao.AvgMeasurementDao;
import com.example.demomk.Dao.LocationDao;
import com.example.demomk.Dao.MeasurementDao;
import com.example.demomk.Dao.UserDao;
import com.example.demomk.entity.AvgMeasurement;
import com.example.demomk.entity.Location;
import com.example.demomk.entity.Measurement;
import com.example.demomk.entity.User;

import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@Database(entities = {User.class, Location.class, Measurement.class, AvgMeasurement.class},
        version = 6,
        exportSchema = true)
public abstract class AppDatabase extends RoomDatabase {
    private static volatile AppDatabase INSTANCE;
    public static ExecutorService databaseWriteExecutor = Executors.newSingleThreadExecutor();

    public abstract UserDao userDao();
    public abstract LocationDao locationDao();
    public abstract MeasurementDao measurementDao();
    public abstract AvgMeasurementDao avgMeasurementDao();

    public static AppDatabase getInstance(Context context) {
        if (INSTANCE == null) {
            synchronized (AppDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                                    AppDatabase.class, "measurement_database")
                            .addCallback(sRoomDatabaseCallback)
                            .addMigrations(MIGRATION_1_2,  MIGRATION_2_3,MIGRATION_3_4,MIGRATION_4_5,MIGRATION_5_6)
                            .fallbackToDestructiveMigration()
                            .build();
                }
            }
        }
        return INSTANCE;
    }
    private static RoomDatabase.Callback sRoomDatabaseCallback = new RoomDatabase.Callback() {
        @Override
        public void onOpen(SupportSQLiteDatabase db) {
            super.onOpen(db);
        }
    };

    static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {

            // 1. 创建临时表，完全匹配预期结构
            database.execSQL("CREATE TABLE temp_measurements (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                    "user_id INTEGER NOT NULL, " +
                    "parent_id INTEGER NOT NULL, " + // 移除了DEFAULT 0
                    "start_time TEXT, " +
                    "end_time TEXT, " +
                    "flow_rate REAL NOT NULL, " + // 添加NOT NULL
                    "value_05 REAL NOT NULL, " +
                    "value_10 REAL NOT NULL, " +
                    "value_20 REAL NOT NULL, " +
                    "value_30 REAL NOT NULL, " +
                    "notes TEXT, " +
                    "created_at INTEGER NOT NULL)"); // 添加NOT NULL

            // 2. 迁移数据，为可能为null的字段提供默认值
            database.execSQL("INSERT INTO temp_measurements " +
                    "(id, user_id, parent_id, start_time, end_time, flow_rate, " +
                    "value_05, value_10, value_20, value_30, notes, created_at) " +
                    "SELECT id, user_id, 0, start_time, end_time, " +
                    "COALESCE(flow_rate, 0), " + // 处理可能的null值
                    "COALESCE(value_05, 0), " +
                    "COALESCE(value_10, 0), " +
                    "COALESCE(value_20, 0), " +
                    "COALESCE(value_30, 0), " +
                    "notes, " +
                    "COALESCE(created_at, 0) " + // 处理可能的null值
                    "FROM measurements");

            // 3. 删除旧表
            database.execSQL("DROP TABLE measurements");

            // 4. 重命名临时表
            database.execSQL("ALTER TABLE temp_measurements RENAME TO measurements");

            // 5. 创建索引
            database.execSQL("CREATE INDEX index_measurements_user_id ON measurements(user_id)");
            database.execSQL("CREATE INDEX index_measurements_start_time ON measurements(start_time)");

            // 6. 创建avg_measurements表
            database.execSQL("CREATE TABLE IF NOT EXISTS `avg_measurements` ("
                    + "`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                    + "`user_id` INTEGER NOT NULL, "
                    + "`location_id` INTEGER NOT NULL, "
                    + "`start_time` TEXT)");
        }
    };
    static final Migration MIGRATION_2_3 = new Migration(2, 3) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            // 执行 ALTER TABLE 语句新增 flow 列
            database.execSQL("ALTER TABLE avg_measurements ADD COLUMN flow REAL DEFAULT 0.0");
        }
    };
    static final Migration MIGRATION_3_4 = new Migration(3, 4) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            //测试记录表新增
            database.execSQL("ALTER TABLE measurements ADD COLUMN  value_100 REAL NOT NULL DEFAULT 0.0");

            //avg新增
            database.execSQL("ALTER TABLE avg_measurements ADD COLUMN  means TEXT DEFAULT  ''");
            database.execSQL("ALTER TABLE avg_measurements ADD COLUMN  laws TEXT DEFAULT ''");
            database.execSQL("ALTER TABLE avg_measurements ADD COLUMN  extra1 TEXT DEFAULT ''");
            database.execSQL("ALTER TABLE avg_measurements ADD COLUMN  extra2 TEXT DEFAULT ''");
            database.execSQL("ALTER TABLE avg_measurements ADD COLUMN  extra3 TEXT DEFAULT ''");
        }
    };

    //修改了数据类型  double->  int、添加了气体字段
    static final Migration MIGRATION_4_5= new Migration(4, 5) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            // 1. 直接删除旧表（连带数据一起丢弃）
            database.execSQL("DROP TABLE IF EXISTS measurements");

            // 2. 按新结构创建表（所有 REAL 改为 INTEGER）
            database.execSQL("CREATE TABLE IF NOT EXISTS measurements (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                    "user_id INTEGER NOT NULL, " +
                    "parent_id INTEGER NOT NULL, " +
                    "start_time TEXT, " +
                    "end_time TEXT, " +
                    "flow_rate INTEGER NOT NULL DEFAULT 0, " +  // 改为 INTEGER
                    "value_03 INTEGER NOT NULL DEFAULT 0, " +  // 改为 INTEGER
                    "value_05 INTEGER NOT NULL DEFAULT 0, " +
                    "value_10 INTEGER NOT NULL DEFAULT 0, " +
                    "value_20 INTEGER NOT NULL DEFAULT 0, " +
                    "value_30 INTEGER NOT NULL DEFAULT 0, " +
                    "value_50 INTEGER NOT NULL DEFAULT 0," +
                    "value_100 INTEGER NOT NULL DEFAULT 0, " +
                    "notes TEXT, " +
                    "created_at INTEGER NOT NULL, " +
                    "extra1 TEXT, " +
                    "extra2 TEXT, " +
                    "extra3 TEXT," +
                    "air TEXT DEFAULT NULL)");

            // 3. 重建索引（根据你的JSON定义）
            database.execSQL("CREATE INDEX IF NOT EXISTS index_measurements_user_id ON measurements (user_id)");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_measurements_start_time ON measurements (start_time)");
        }
    };

    static final Migration MIGRATION_5_6= new Migration(5, 6) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            // 1. 直接删除旧表（连带数据一起丢弃）
            database.execSQL("DROP TABLE IF EXISTS measurements");

            // 2. 按新结构创建表（所有 REAL 改为 INTEGER）
            database.execSQL("CREATE TABLE IF NOT EXISTS measurements (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                    "user_id INTEGER NOT NULL, " +
                    "parent_id INTEGER NOT NULL, " +
                    "start_time TEXT, " +
                    "end_time TEXT, " +
                    "flow_rate REAL NOT NULL DEFAULT 0.0, " +  // 改为 INTEGER
                    "value_03 INTEGER NOT NULL DEFAULT 0, " +  // 改为 INTEGER
                    "value_05 INTEGER NOT NULL DEFAULT 0, " +
                    "value_10 INTEGER NOT NULL DEFAULT 0, " +
                    "value_20 INTEGER NOT NULL DEFAULT 0, " +
                    "value_30 INTEGER NOT NULL DEFAULT 0, " +
                    "value_50 INTEGER NOT NULL DEFAULT 0," +
                    "value_100 INTEGER NOT NULL DEFAULT 0, " +
                    "notes TEXT, " +
                    "created_at INTEGER NOT NULL, " +
                    "extra1 TEXT, " +
                    "extra2 TEXT, " +
                    "extra3 REAL NOT NULL DEFAULT 0.0," +
                    "air TEXT DEFAULT NULL)");

            // 3. 重建索引（根据你的JSON定义）
            database.execSQL("CREATE INDEX IF NOT EXISTS index_measurements_user_id ON measurements (user_id)");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_measurements_start_time ON measurements (start_time)");
        }
    };

    @VisibleForTesting
    public static void destroyInstance() {
        INSTANCE = null;
    }

}
